package com.itown.expo.controller;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.ArrayUtils;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.springframework.web.servlet.ModelAndView;

import webframe.common.dao.BaseJDBCDao;
import webframe.common.dao.Pagination;
import webframe.common.helper.ControllerHelper;
import webframe.common.helper.FormatTransformHelper;

import com.itown.expo.controller.base.MyBaseCommandController;
import com.itown.expo.helper.MyControllerHelper;
import com.itown.expo.model.Container;
import com.itown.expo.model.Decl;

public class StatDeclsController extends MyBaseCommandController {
	private static Order[] orders = new Order[] { Order.desc("id") };

	@Override
	protected ModelAndView handleInner(HttpServletRequest request,
			HttpServletResponse response, Object command) {
		Decl cdecl = (Decl) command;
		DetachedCriteria criteria = MyControllerHelper
				.getDeclDetachedCriteria(cdecl);

		applyCriteria(criteria, cdecl);

		Map map = new HashMap();
		Pagination<Decl> page = ControllerHelper.queryPaginationWithSql(
				request, orders, criteria);

		String onStat = cdecl.getFeeStatus();
		if (onStat != null) {
			Object[] sqls = page.getSqlAndParams(true);
			String where = (String) sqls[0];
			Object[] params = (Object[]) sqls[1];

			map.put("statCounts", statDecl(page, where, params));
			map.put("statContainer", statContainer(where, params));
			map.put("statEpidemicContainer", statEpidemicContainer(where,
					params));
		}
		map.put("appPrefix", getAppPrefix());
		map.put("page", page);
		map.put("cdecl", cdecl);

		return new ModelAndView("StatDecls.jsp", "map", map);
	}

	// 应用查询条件
	public static void applyCriteria(DetachedCriteria criteria, Decl cdecl) {
		ControllerHelper.addEqualRestrictions(criteria, "id", cdecl.getId());
		ControllerHelper.addLikeRestrictions(criteria, "declNo", cdecl
				.getDeclNo());
		ControllerHelper.addEqualRestrictions(criteria, "backupCode", cdecl
				.getBackupCode());
		ControllerHelper.addLikeRestrictions(criteria, "entName", cdecl
				.getEntName());

		ControllerHelper
				.addORLikeRestrictions(criteria, new String[] {
						"consignorCname", "consignorEname" }, cdecl
						.getConsignorCname());
		ControllerHelper
				.addORLikeRestrictions(criteria, new String[] {
						"consigneeCname", "consigneeEname" }, cdecl
						.getConsigneeCname());

		ControllerHelper.addEqualRestrictions(criteria, "tradeModeCode", cdecl
				.getTradeModeCode());
		ControllerHelper.addEqualRestrictions(criteria, "tradeCountryCode",
				cdecl.getTradeCountryCode());
		ControllerHelper.addEqualRestrictions(criteria, "transTypeCode", cdecl
				.getTransTypeCode());

		if (cdecl.getInspDept1() != null && cdecl.getInspDept1().length() > 6) {
			// cdecl.setInspDept1(cdecl.getInspDept1().substring(6));
			ControllerHelper.addEqualRestrictions(criteria, "inspDept1", cdecl
					.getInspDept1().substring(6));
			cdecl.setInspOrgCode(cdecl.getInspDept1().substring(0, 6));
			ControllerHelper.addEqualRestrictions(criteria, "inspOrgCode",
					cdecl.getInspOrgCode());
		} else {
			ControllerHelper.addEqualRestrictions(criteria, "inspOrgCode",
					cdecl.getInspOrgCode());
		}

		ControllerHelper.addEqualRestrictions(criteria, "colligateResult",
				cdecl.getColligateResult());
		// 报检日期范围
		ControllerHelper.addBetweenRestrictions(criteria, "declDate",
				FormatTransformHelper.string2Date(cdecl.getInspDept4()),
				FormatTransformHelper.string2Date(cdecl.getInspDept5()));

		// 监管结果
		if (ControllerHelper.isSafe(cdecl.getMarkNo())) {
			ControllerHelper.addEqualRestrictions(criteria.createAlias(
					"supervisePlan", "sp"), "sp.result", Long.parseLong(cdecl
					.getMarkNo()));
		}
		// return criteria;
	}

	private List<Container> statContainer(String where, Object[] params) {
		List<Container> clist = new ArrayList<Container>();
		List<String> containerModel = getServiceContainer()
				.getCheckBizService().findDistinctContainerModel();

		String sqlContainerModel = " and c.CONTAINER_MODEL_CODE=? ";
		// 下sql同statDecl
		String subQuery = "(select t.decl_no from decl t " + where + ")";
		String sql4 = "select sum(c.CONTAINER_QTY) from container c where c.decl_no in "
				+ subQuery;
		String sql8 = "select sum(c.CONTAINER_QTY) from container_check c where c.decl_no in"
				+ subQuery
				+ "and (c.CONTAINER_RESULT_CODE is null or c.CONTAINER_RESULT_CODE ='1')";
		String sql9 = "select sum(c.CONTAINER_QTY) from container_check c where c.decl_no in"
				+ subQuery + "and (c.CONTAINER_RESULT_CODE in ('2','3','4'))";
		String sql10 = "select sum(c.CONTAINER_QTY) from container_check c where c.decl_no in"
				+ subQuery + "and (c.CONTAINER_RESULT_CODE in ('7','8','9'))";

		for (String cm : containerModel) {
			Container con = new Container();
			con.setContainerModelCode(cm);
			// 全部
			con.getMap().put(
					"all",
					preformQuery(sql4 + sqlContainerModel, ArrayUtils.addAll(
							params, new String[] { cm })));
			// 合格
			con.getMap().put(
					"good",
					preformQuery(sql8 + sqlContainerModel, ArrayUtils.addAll(
							params, new String[] { cm })));
			// 处理合格
			con.getMap().put(
					"beGood",
					preformQuery(sql9 + sqlContainerModel, ArrayUtils.addAll(
							params, new String[] { cm })));
			// 不合格
			con.getMap().put(
					"bad",
					preformQuery(sql10 + sqlContainerModel, ArrayUtils.addAll(
							params, new String[] { cm })));

			if ((Long) con.getMap().get("all") > 0) {
				clist.add(con);
			}
		}
		return clist;
	}

	private List<Container> statEpidemicContainer(String where, Object[] params) {
		List<Container> clist = new ArrayList<Container>();
		List<String> containerModel = getServiceContainer()
				.getCheckBizService().findDistinctContainerModel();
		String w = "";
		if (where.length() != 0) {
			w = where.replace("where", "");
		}

		StringBuffer sb = new StringBuffer();
		{
			sb.append("where x.decl_no in(");
			sb.append("select t.decl_no from decl t where ");
			if (w.length() != 0) {
				sb.append(w);
				sb.append(" and ");
			}
			sb
					.append(" t.decl_no in (select distinct c.decl_no from container c where c.container_model_code=?) ");
			sb.append("and (");
			sb
					.append("t.decl_no in(select distinct d.decl_no from decl d where d.trade_country_code in (@@@))");
			sb
					.append("or t.decl_no in(select distinct g.decl_no from goods g where g.origin_country_code in(@@@))");
			sb.append("))");
		}

		// 报检批
		String h1 = "select count(x.decl_no) from decl x ";
		// 集装箱数
		String h2 = "select sum(x.container_qty) from container x ";
		// conditons
		String cond1 = " select distinct e.country_code from epidemic_notify e ";
		String cond2 = " select distinct e.country_code from epidemic_notify e where e.kind=? ";
		String cond3 = " and x.container_model_code=?";
		String sql1 = sb.toString().replaceAll("@@@", cond1);
		String sql2 = sb.toString().replaceAll("@@@", cond2);

		// 61-动物检疫 62-植物检疫 63-卫生检疫
		for (String cm : containerModel) {
			Container con = new Container();
			con.setContainerModelCode(cm);

			// 报检批
			con.getMap().put(
					"sumDecl",
					preformQuery(h1 + sql1, ArrayUtils.addAll(params,
							new String[] { cm })));
			con.getMap().put(
					"sumCon",
					preformQuery(h2 + sql1 + cond3, ArrayUtils.addAll(params,
							new String[] { cm, cm })));
			// 动物
			con.getMap().put(
					"animalDecl",
					preformQuery(h1 + sql2, ArrayUtils.addAll(params,
							new String[] { cm, "61", "61" })));
			con.getMap().put(
					"animalCon",
					preformQuery(h2 + sql2 + cond3, ArrayUtils.addAll(params,
							new String[] { cm, "61", "61", cm })));
			// 植物
			con.getMap().put(
					"plantDecl",
					preformQuery(h1 + sql2, ArrayUtils.addAll(params,
							new String[] { cm, "62", "62" })));
			con.getMap().put(
					"plantCon",
					preformQuery(h2 + sql2 + cond3, ArrayUtils.addAll(params,
							new String[] { cm, "62", "62", cm })));
			// 卫生
			con.getMap().put(
					"healthDecl",
					preformQuery(h1 + sql2, ArrayUtils.addAll(params,
							new String[] { cm, "63", "63" })));
			con.getMap().put(
					"healthCon",
					preformQuery(h2 + sql2 + cond3, ArrayUtils.addAll(params,
							new String[] { cm, "63", "63", cm })));

			if ((Long) con.getMap().get("sumDecl") > 0) {
				clist.add(con);
			}
		}
		return clist;
	}

	private List statDecl(Pagination<Decl> page, String where, Object[] params) {
		String subQuery = "(select t.decl_no from decl t " + where + ")";
		String sql2 = "select count(d.id)  from goods d where d.decl_no in "
				+ subQuery;
		String sql3 = "select sum(VALUES_USD) from decl t " + where;
		String sql4 = "select sum(c.CONTAINER_QTY) from container c where c.decl_no in "
				+ subQuery;

		String sql5 = "select count(t.id) from decl t "
				+ genWhereSql(where,
						"(t.COLLIGATE_RESULT is null or t.COLLIGATE_RESULT ='1')");
		String sql6 = "select count(t.id) from decl t "
				+ genWhereSql(where, "t.COLLIGATE_RESULT in ('2','3','4')");
		String sql7 = "select count(t.id) from decl t "
				+ genWhereSql(where, "t.COLLIGATE_RESULT in ('7','8','9')");

		String sql8 = "select sum(c.CONTAINER_QTY) from container_check c where c.decl_no in"
				+ subQuery
				+ "and (c.CONTAINER_RESULT_CODE is null or c.CONTAINER_RESULT_CODE ='1')";
		String sql9 = "select sum(c.CONTAINER_QTY) from container_check c where c.decl_no in"
				+ subQuery + "and (c.CONTAINER_RESULT_CODE in ('2','3','4'))";
		String sql10 = "select sum(c.CONTAINER_QTY) from container_check c where c.decl_no in"
				+ subQuery + "and (c.CONTAINER_RESULT_CODE in ('7','8','9'))";

		List counts = new ArrayList();
		counts.add(page.getTotalLineCount());
		counts.add(preformQuery(sql2, params));
		counts.add(preformQuery(sql3, params));
		counts.add(preformQuery(sql4, params));
		counts.add(preformQuery(sql5, params));
		counts.add(preformQuery(sql6, params));
		counts.add(preformQuery(sql7, params));
		counts.add(preformQuery(sql8, params));
		counts.add(preformQuery(sql9, params));
		counts.add(preformQuery(sql10, params));
		return counts;
	}

	private long preformQuery(String nativeSql, Object[] params) {
		BaseJDBCDao dao = getServiceContainer().getMyBaseService().getJdbcDao();
		long l = dao.getJdbcTemplate().queryForLong(nativeSql, params);
		return l;
	}

	private String genWhereSql(String where, String add) {
		String result;
		if (where == null || where.length() == 0) {
			result = " where " + add;
		} else {
			result = where + " and " + add;
		}
		return result;
	}
}